package cn.edu.sanxiau.www.dao.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.sanxiau.www.dao.CourseDao;
import cn.edu.sanxiau.www.model.Course;
import cn.edu.sanxiau.www.model.PaiKe;
import cn.edu.sanxiau.www.model.User2;
import frame.utils.dbutil.JdbcUtils;

public class CourseDaoImpl implements CourseDao {

	public List<Course> queryAllCourse() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM course";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			List<Course> course_db_list = new ArrayList<Course>();

			while (rs.next()) {
				Course course_db = new Course();
				course_db.setCourseId(rs.getInt("courseId"));
				course_db.setCourseName(rs.getString("courseName"));
				course_db.setCourseTeacher(rs.getString("courseTeacher"));
				course_db.setScore(rs.getString("score"));

				course_db_list.add(course_db);
			}

			return course_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
    }

	@Override
	public int addCourseByCourse(Course course) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "INSERT INTO  course (courseId,courseName,courseTeacher,score) VALUES(?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, course.getCourseId());
			pstmt.setString(2, course.getCourseName());
			pstmt.setString(3, course.getCourseTeacher());
			pstmt.setString(4, course.getScore());

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;

    }

	@Override
	public int deleteCourseByCourseId(int courseId) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();

			String sql = "DELETE FROM course WHERE courseId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, courseId);

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public Course queryCourseByCourseId(int courseId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM course WHERE courseId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, courseId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Course course_db = new Course();
				course_db.setCourseId(rs.getInt("courseId"));
				course_db.setCourseName(rs.getString("courseName"));
				course_db.setCourseTeacher(rs.getString("courseTeacher"));
				course_db.setScore(rs.getString("score"));
				return course_db;
			}
		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int updateCourseByCourse(Course course) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();
			String sql = "UPDATE course SET courseName=?,courseTeacher=?,score=? WHERE courseId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, course.getCourseName());
			pstmt.setString(2, course.getCourseTeacher());
			pstmt.setString(3, course.getScore());
			pstmt.setInt(4, course.getCourseId());
			int m = pstmt.executeUpdate();
			return m;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int queryUser2TotalRecord() {
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;

			try {
				conn = JdbcUtils.getConnection();
				String sql = "SELECT count(*) FROM  course";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if (rs.next()) {
					int count = rs.getInt(1);
					return count;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return 0;
	}

	@Override
	public List<Course> queryCurrentPageDataList(int i, int ps) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM  course LIMIT ?,?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, i);
			pstmt.setInt(2, ps);
			rs = pstmt.executeQuery();
			List<Course> courseList = new ArrayList<>();
			while (rs.next()) {
				Course course_db = new Course();
				/*user2_db.setUserId(rs.getInt("userId"));*/
				course_db.setCourseId(rs.getInt("courseId"));
				course_db.setCourseName(rs.getString("courseName"));
				course_db.setCourseTeacher(rs.getString("courseTeacher"));
				course_db.setScore(rs.getString("score"));
				courseList.add(course_db);
			}
			return courseList;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public List<Course> queryAllCourseByState(String state) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM course where state=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,state);
			rs = pstmt.executeQuery();

			List<Course> course_db_list = new ArrayList<Course>();

			while (rs.next()) {
				Course course_db = new Course();
				course_db.setCourseId(rs.getInt("courseId"));
				course_db.setCourseName(rs.getString("courseName"));
				course_db.setCourseTeacher(rs.getString("courseTeacher"));
				course_db.setScore(rs.getString("score"));
				course_db.setState(rs.getString("state"));
				course_db_list.add(course_db);
			}

			return course_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	
	//shi/////////////////////////////////
	
	@Override
	public List<PaiKe> queryPkBycourseId() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT room_course.date, room_course.room_course_Id, classroom.roomId, classroom.roomName, room_course.time, course.courseId, course.courseName, course.courseTeacher, course.score, course.state FROM (classroom , course) INNER JOIN room_course ON course.courseId = room_course.courseId AND room_course.roomId = classroom.roomId";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			List<PaiKe> paike_db_list = new ArrayList<PaiKe>();

			while (rs.next()) {
				PaiKe paike_db = new PaiKe();
				paike_db.setDate(rs.getString("room_course.date"));
				paike_db.setRoomCourseId(rs.getInt("room_course.room_course_Id"));
				paike_db.setRoomId(rs.getInt("classroom.roomId"));
				paike_db.setTime(rs.getString("room_course.time"));
				paike_db.setRoomName(rs.getString("classroom.roomName"));
				paike_db.setCourseId(rs.getInt("course.courseId"));
				paike_db.setCourseName(rs.getString("course.courseName"));
				paike_db.setCourseTeacher(rs.getString("course.courseTeacher"));
				paike_db.setScore(rs.getString("course.score"));
				paike_db.setState(rs.getString("course.state"));
				paike_db_list.add(paike_db);
			}

			return paike_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int updateCourseStateByCourseId(int courseId) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();
			String sql = "UPDATE course SET state=? WHERE courseId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "0");
			pstmt.setInt(2, courseId);

			int n = pstmt.executeUpdate();
			return n;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<PaiKe> queryPkBycourseId(int courseId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT classroom.roomId,room_course.room_course_Id, classroom.roomName, course.courseName, course.courseTeacher, room_course.date, room_course.time, course.courseId FROM (classroom , course) INNER JOIN room_course ON course.courseId = room_course.courseId AND room_course.roomId = classroom.roomId WHERE course.courseId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, courseId);
			rs = pstmt.executeQuery();

			List<PaiKe> paike_db_list = new ArrayList<PaiKe>();

			while (rs.next()) {
				PaiKe paike_db = new PaiKe();
				paike_db.setDate(rs.getString("room_course.date"));
				paike_db.setRoomCourseId(rs.getInt("room_course.room_course_Id"));
				paike_db.setRoomId(rs.getInt("classroom.roomId"));
				paike_db.setTime(rs.getString("room_course.time"));
				paike_db.setRoomName(rs.getString("classroom.roomName"));
				paike_db.setCourseId(rs.getInt("course.courseId"));
				paike_db.setCourseName(rs.getString("course.courseName"));
				paike_db.setCourseTeacher(rs.getString("course.courseTeacher"));
				paike_db_list.add(paike_db);
			}

			return paike_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int updateCourseByPaiKe(PaiKe paike) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		PreparedStatement pstmt3 = null;

		try {
			conn = JdbcUtils.getConnection();
			String sql = "UPDATE course SET state=? WHERE courseId=?";
			String sql1 = "UPDATE classroom SET state=? ,date=? WHERE roomId=?";
			String sql2 = "UPDATE room_course SET roomId=?, time=?, date=? WHERE courseId=?";
			String sql3 = "UPDATE classroom SET state=? ,date=? WHERE roomId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt1 = conn.prepareStatement(sql1);
			pstmt2 = conn.prepareStatement(sql2);
			pstmt3 = conn.prepareStatement(sql3);
			
			pstmt.setString(1, paike.getTime());
			pstmt.setInt(2, paike.getCourseId());
			
			pstmt1.setString(1, paike.getTime());
			pstmt1.setString(2, paike.getDate());
			pstmt1.setInt(3, paike.getRoomId());
			
			pstmt2.setInt(1, paike.getRoomId());
			pstmt2.setString(2, paike.getTime());
			pstmt2.setString(3, paike.getDate());
			pstmt2.setInt(4, paike.getCourseId());

			pstmt3.setString(1, "0");
			pstmt3.setString(2, "0");
			pstmt3.setInt(3, paike.getPreroomId());
			int m = pstmt.executeUpdate();
			pstmt1.executeUpdate();
			pstmt2.executeUpdate();
			pstmt3.executeUpdate();
			return m;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}
}
